using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.ObjectBuilder;
using GC2.ExceptionHandler;
using GC2.ExceptionHandler.Services;
using GC2.Entities.Logistica;
using GC2.DataAccess;

namespace GC2.DataAccess.DALogistica
{
    public class DAHRuta
    {
        private Database _database;        // Objeto DataBase del Data Access Application Block

        #region Propiedades miembro
        /// <summary>
        /// Seteo la propiedad _database de la clase.
        /// </summary>
        private Database DataBase
        {
            get { return _database; }
            set { _database = value; }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Contructor de la clase sin parametro. Crea un objeto DataBase
        /// del Data AppBlock tomando el defaultDatabase del app.config.
        /// </summary>
        //public AplicacionesDataAccess()
        //{
        //    this.DataBase = DatabaseFactory.CreateDatabase();
        //}
        #endregion

        #region DAHRutaProximoID
        public Int64 DAHRutaProximoID(string Connection)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("ProximoID");
            Int64 iProximo = 0;
            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@tabla", DbType.String, "HRuta");
                    DataBase.AddInParameter(cmd, "@campo", DbType.String, "IdHRuta");
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        iProximo = Convert.ToInt64(Odbreader["ID_IdHRuta"]);
                    }
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
            return iProximo;
        }
        #endregion

        #region DAHRutaInsert
        /// <summary>
        /// Inserta una nuevo Hoja de Ruta
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaInsert(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_Insert");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);
                    DataBase.AddInParameter(cmd, "@CantPaginas", DbType.Int16, hruta.CantPaginas);
                    DataBase.AddInParameter(cmd, "@Items", DbType.Int16, hruta.Items);
                    DataBase.AddInParameter(cmd, "@Sucursal", DbType.Int16, hruta.Sucursal);
                    DataBase.AddInParameter(cmd, "@Bultos", DbType.Int16, hruta.Bultos);
                    DataBase.AddInParameter(cmd, "@Kilos", DbType.Decimal, hruta.Kilos);
                    DataBase.AddInParameter(cmd, "@PorCobrar", DbType.Decimal, hruta.PorCobrar);
                    DataBase.AddInParameter(cmd, "@NoModificar", DbType.Int16, hruta.NoModificar);
                    DataBase.AddInParameter(cmd, "@Entregar", DbType.Int16, hruta.Entregar);
                    DataBase.AddInParameter(cmd, "@Imputar", DbType.Int16, hruta.Imputar);
                    DataBase.AddInParameter(cmd, "@Origen", DbType.String, hruta.Origen);
                    DataBase.AddInParameter(cmd, "@Destino", DbType.String, hruta.Destino);
                    DataBase.AddInParameter(cmd, "@Patente", DbType.String, hruta.Patente);
                    DataBase.AddInParameter(cmd, "@TipoRodado", DbType.String, hruta.TipoRodado);
                    DataBase.AddInParameter(cmd, "@Responsable", DbType.String, hruta.Responsable);
                    DataBase.AddInParameter(cmd, "@Fecha", DbType.DateTime, hruta.Fecha);
                    DataBase.AddInParameter(cmd, "@FechaSalida", DbType.DateTime, hruta.FechaSalida);
                    DataBase.AddInParameter(cmd, "@HoraSalida", DbType.Int16, hruta.HoraSalida);
                    DataBase.AddInParameter(cmd, "@FechaLLegada", DbType.DateTime, hruta.FechaLLegada);
                    DataBase.AddInParameter(cmd, "@HoraLLegada", DbType.Int16, hruta.HoraLLegada);
                    DataBase.AddInParameter(cmd, "@Id_Usuario", DbType.Int16, hruta.Id_Usuario);
                    DataBase.AddInParameter(cmd, "@Obs", DbType.String, hruta.Obs);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaGetOne
        /// <summary>
        /// Devuelve una HRuta por su IdHRuta
        /// </summary>
        /// <param name="Connection"></param>
        /// <returns>"HRuta"</returns>
        public HRuta DAHRutaGetOne(string Connection, HRuta phruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_GetOne");
            
            try
            {
                HRuta hruta = new HRuta();
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, phruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int64, phruta.Pagina);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                    }
                }
                return hruta;
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaGetAll
        /// <summary>
        /// Devuelve Lista de HRuta solo las Paginas 0 ( Cabeceras )
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        /// <returns>HRuta</returns>
        public List<HRuta> DAHRutaGetAll(string Connection, HRuta hrutaD, HRuta hrutaH)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_GetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                DataBase.AddInParameter(cmd, "@FechaD", DbType.DateTime, hrutaD.Fecha);
                DataBase.AddInParameter(cmd, "@FechaH", DbType.DateTime, hrutaH.Fecha);
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaActivasGetAll
        /// <summary>
        /// Devuelve Lista de HRuta solo las Paginas 0 ( Cabeceras ) que esten Activas
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        /// <returns><List>"HRuta"</List></returns>
        public List<HRuta> DAHRutaActivasGetAll(string Connection, HRuta hrutaD, HRuta hrutaH)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_ActivasGetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                DataBase.AddInParameter(cmd, "@FechaD", DbType.DateTime, hrutaD.Fecha);
                DataBase.AddInParameter(cmd, "@FechaH", DbType.DateTime, hrutaH.Fecha);
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaPendientesGetAll
        /// <summary>
        /// Devuelve Lista de HRuta solo las Paginas 0 ( Cabeceras ) que esten Pendientes de Activar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        /// <returns><List>"HRuta"</List></returns>
        public List<HRuta> DAHRutaPendientesGetAll(string Connection)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_PendientesGetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaCerradasGetAll
        /// <summary>
        /// Devuelve Lista de HRuta solo las Paginas 0 ( Cabeceras ) que esten Activas
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        /// <returns><List>"HRuta"</List></returns>
        public List<HRuta> DAHRutaCerradasGetAll(string Connection, HRuta hrutaD, HRuta hrutaH)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_CerradasGetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                DataBase.AddInParameter(cmd, "@FechaD", DbType.DateTime, hrutaD.Fecha);
                DataBase.AddInParameter(cmd, "@FechaH", DbType.DateTime, hrutaH.Fecha);
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaEntregarGetAll
        /// <summary>
        /// Devuelve Lista de HRuta a pendiente de Entrega
        /// </summary>
        /// <param name="Connection"></param>
        /// <returns><List>"HRuta"</List></returns>
        public List<HRuta> DAHRutaEntregarGetAll(string Connection)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_EntregarGetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        hruta.IdEstado = 1;
                        hruta.Estado = "Activa";
                        TimeSpan tpDias = new TimeSpan();
                        tpDias = DateTime.Now - hruta.Fecha;
                        hruta.Dias = tpDias.Days.ToString();

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaImputarGetAll
        /// <summary>
        /// Devuelve Lista de HRuta a pendiente de Entrega
        /// </summary>
        /// <param name="Connection"></param>
        /// <returns><List>"HRuta"</List></returns>
        public List<HRuta> DAHRutaImputarGetAll(string Connection)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_ImputarGetAll");

            List<HRuta> listHRuta = new List<HRuta>();
            try
            {
                using (cmd)
                {
                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();
                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        hruta.IdEstado = 1;
                        hruta.Estado = "Activa";
                        TimeSpan tpDias = new TimeSpan();
                        tpDias = DateTime.Now - hruta.Fecha;
                        hruta.Dias = tpDias.Days.ToString();

                        listHRuta.Add(hruta);
                    }

                    return listHRuta;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaUpdate
        /// <summary>
        /// Actualiza una Hoja de Ruta
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaUpdate(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_Update");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);
                    DataBase.AddInParameter(cmd, "@CantPaginas", DbType.Int16, hruta.CantPaginas);
                    DataBase.AddInParameter(cmd, "@Items", DbType.Int16, hruta.Items);
                    DataBase.AddInParameter(cmd, "@Sucursal", DbType.Int16, hruta.Sucursal);
                    DataBase.AddInParameter(cmd, "@Bultos", DbType.Int16, hruta.Bultos);
                    DataBase.AddInParameter(cmd, "@Kilos", DbType.Decimal, hruta.Kilos);
                    DataBase.AddInParameter(cmd, "@PorCobrar", DbType.Decimal, hruta.PorCobrar);
                    DataBase.AddInParameter(cmd, "@NoModificar", DbType.Int16, hruta.NoModificar);
                    DataBase.AddInParameter(cmd, "@Entregar", DbType.Int16, hruta.Entregar);
                    DataBase.AddInParameter(cmd, "@Imputar", DbType.Int16, hruta.Imputar);
                    DataBase.AddInParameter(cmd, "@Origen", DbType.String, hruta.Origen);
                    DataBase.AddInParameter(cmd, "@Destino", DbType.String, hruta.Destino);
                    DataBase.AddInParameter(cmd, "@Patente", DbType.String, hruta.Patente);
                    DataBase.AddInParameter(cmd, "@TipoRodado", DbType.String, hruta.TipoRodado);
                    DataBase.AddInParameter(cmd, "@Responsable", DbType.String, hruta.Responsable);
                    DataBase.AddInParameter(cmd, "@Fecha", DbType.DateTime, hruta.Fecha);
                    DataBase.AddInParameter(cmd, "@FechaSalida", DbType.DateTime, hruta.FechaSalida);
                    DataBase.AddInParameter(cmd, "@HoraSalida", DbType.Int16, hruta.HoraSalida);
                    DataBase.AddInParameter(cmd, "@FechaLLegada", DbType.DateTime, hruta.FechaLLegada);
                    DataBase.AddInParameter(cmd, "@HoraLLegada", DbType.Int16, hruta.HoraLLegada);
                    DataBase.AddInParameter(cmd, "@Id_Usuario", DbType.Int16, hruta.Id_Usuario);
                    DataBase.AddInParameter(cmd, "@Obs", DbType.String, hruta.Obs);


                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaUpdateEntregar
        /// <summary>
        /// Actualiza una Hoja de Ruta columna Entregar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaUpdateEntregar(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_UpdateEntregar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);
                    DataBase.AddInParameter(cmd, "@Entregar", DbType.Int16, hruta.Entregar);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaUpdateImputar
        /// <summary>
        /// Actualiza una Hoja de Ruta columna Imputar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaUpdateImputar(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_UpdateImputar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);
                    DataBase.AddInParameter(cmd, "@Imputar", DbType.Int16, hruta.Imputar);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaUpdateNoModificar
        /// <summary>
        /// Actualiza una Hoja de Ruta columna NoModificar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaUpdateNoModificar(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_UpdateNoModificar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);
                    DataBase.AddInParameter(cmd, "@NoModificar", DbType.Int16, hruta.NoModificar);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaGetPaginas
        /// <summary>
        /// Devuelve lista de Paginas de una IdHRuta
        /// </summary>
        /// <param name="Connection"></param>
        /// <returns>"HRuta"</returns>
        public List<HRuta> DAHRutaGetPaginas(string Connection, HRuta phruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_GetPaginas");

            try
            {
                List<HRuta> listaHRuta = new List<HRuta>();
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, phruta.IdHRuta);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRuta hruta = new HRuta();

                        hruta.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        hruta.Pagina = (int)Odbreader["Pagina"];
                        hruta.CantPaginas = (int)Odbreader["CantPaginas"];
                        hruta.Items = (int)Odbreader["Items"];
                        hruta.Sucursal = (int)Odbreader["Sucursal"];
                        hruta.Bultos = (int)Odbreader["Bultos"];
                        hruta.Kilos = (Decimal)Odbreader["Kilos"];
                        hruta.PorCobrar = (Decimal)Odbreader["PorCobrar"];
                        hruta.NoModificar = (int)Odbreader["NoModificar"];
                        hruta.Entregar = (int)Odbreader["Entregar"];
                        hruta.Imputar = (int)Odbreader["Imputar"];
                        hruta.Origen = (string)Odbreader["Origen"];
                        hruta.Destino = (string)Odbreader["Destino"];
                        hruta.Patente = (string)Odbreader["Patente"];
                        hruta.TipoRodado = (string)Odbreader["TipoRodado"];
                        hruta.Responsable = (string)Odbreader["Responsable"];
                        hruta.Fecha = (DateTime)Odbreader["Fecha"];
                        hruta.FechaSalida = (DateTime)Odbreader["FechaSalida"];
                        hruta.HoraSalida = Convert.ToInt16(Odbreader["HoraSalida"]);
                        hruta.FechaLLegada = (DateTime)Odbreader["FechaLLegada"];
                        hruta.HoraLLegada = Convert.ToInt16(Odbreader["HoraLLegada"]);
                        hruta.Id_Usuario = (int)Odbreader["Id_Usuario"];
                        hruta.Obs = (string)Odbreader["Obs"];

                        if (hruta.NoModificar == 1)
                        {
                            if (hruta.Entregar == 0 && hruta.Imputar == 0)
                            {
                                hruta.IdEstado = 2;
                                hruta.Estado = "Cerrada";
                            }
                            else
                            {
                                hruta.IdEstado = 1;
                                hruta.Estado = "Activa";
                                TimeSpan tpDias = new TimeSpan();
                                tpDias = DateTime.Now - hruta.Fecha;
                                hruta.Dias = tpDias.Days.ToString();
                            }
                        }
                        else
                        {
                            hruta.IdEstado = 0;
                            hruta.Estado = "Pendiente";
                        }

                        listaHRuta.Add(hruta);

                    }
                }
                return listaHRuta;
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDelete
        /// <summary>
        /// Elimina una HRuta en la tabla HRuta 
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRuta"></param>
        public void DAHRutaDelete(string Connection, HRuta hruta)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRuta_Delete");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hruta.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hruta.Pagina);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

    }
}
